package cn.com.callback

import groovy.json.*;
import groovy.swing.impl.DefaultAction;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.RequestDispatcher;
import cn.com.elite.EliteBean;
import java.security.MessageDigest;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.wisdge.dataservice.Result;
import java.util.Map;
import java.util.Date;
import java.util.HashMap;
import cn.com.elite.Util;
import cn.com.kh.UcisXML;
import java.net.URLEncoder;

import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;

/**
 *
 * @Title: BigdateCallMonth
 * @Description:大数据定时更新每月话务数据
 * @author: Yuchieh 
 * @date 2021-07-13
 *
 */


public class BigdateCallMonth implements ServiceBackInvoke{
	private static final Log log=LogFactory.getLog(BigdateCallMonth.class);
	public String doCallBack(EliteBean eliteBean,HttpServletRequest request,HttpServletResponse response){
				
		response.setCharacterEncoding("UTF-8");
		JSONObject resulterror = new JSONObject();
				

		try{
			//将数据写进his表
			def todaysql_bak = "INSERT INTO bigdatecall_month_his SELECT *,GETDATE() FROM bigdatecall_month";
			//eliteBean.update(todaysql_bak, "dataSource");
			//清空表数据
			def todaysql_del = "TRUNCATE TABLE bigdatecall_month";
			eliteBean.update(todaysql_del, "dataSource");
			//写入数据
			def todaysql_ins1 = "INSERT INTO bigdatecall_month Select ga.area_desc 区域, SUM(case when callresult = 1  THEN 1 ELSE 0 END) As '来电总数', case when SUM(case when callresult = 1  THEN 1 ELSE 0 END)=0 then '0%' ELSE CONVERT(VARCHAR,round(CAST((SUM(CASE WHEN callresult = 1  and firstivrport != '' and Firstagentcdn = '' and Firstagentqueue = '' and Firstagent='' THEN 1 ELSE 0 END) + Sum(Case	When Firstagentcdn != ''  And	Firstagent != '' And Firstagentqueueabandoned = 0 And	Firstagentabandoned = 0 Then	1	Else	0	End)) AS FLOAT) / SUM(case when callresult = 1  THEN 1 ELSE 0 END) * 100,2))  + '%' end as 来电接通率, Sum(Case	When (Firstagentcdn != '' OR Firstagentqueue != '') Then	1	Else	0	End) 人工来电数,case when isnull(Sum(Case	When (Firstagentcdn != '' OR Firstagentqueue != '')  Then	1	Else	0	End),0)=0 then '0%' ELSE CONVERT (VARCHAR,round(CAST ((isnull(Sum(Case	When Firstagentcdn != ''  And	Firstagent != '' And Firstagentqueueabandoned = 0 And	Firstagentabandoned = 0 Then	1	Else	0	End),0)) AS FLOAT) / isnull(Sum(Case	When (Firstagentcdn != '' OR Firstagentqueue != '') Then	1	Else	0	End),0) * 100,2)) + '%' end  as 人工来电接通率,Sum(Case	When Firstagentcdn != '' AND t1.rk = 1 And	Firstagent != '' And Firstagentqueueabandoned = 0 And	Firstagentabandoned = 0 and sikvd.rk = 1 And Datediff(Ss, Firstivrendtime, Firstringingendtime) <= 20  Then	1	Else	0	End) '20秒内接通的来电数',CAST(ROUND(CASE WHEN isnull(	Sum(Case	When (Firstagentcdn != '' OR Firstagentqueue != '') AND t1.rk = 1 and sikvd.rk=1 Then	1	Else	0	End),0)=0 THEN  '0' ELSE Sum(Case	When Firstagentcdn != '' AND t1.rk = 1 And	Firstagent != '' And Firstagentqueueabandoned = 0 And	Firstagentabandoned = 0 and sikvd.rk = 1 And Datediff(Ss, Firstivrendtime, Firstringingendtime) <= 20  Then	1	Else	0	End) * 100.0 / 	Sum(Case	When (Firstagentcdn != '' OR Firstagentqueue != '') AND t1.rk = 1 and sikvd.rk=1 Then	1	Else	0	End) END ,2)as numeric(5,2)) '20秒内接通率','','' FROM Call_Detail_View t1 WITH(nolock) LEFT JOIN szwg_ivr_keyinfo_viewdesc sikvd on sikvd.recguid = t1.ucid and sikvd.rk = 1 LEFT JOIN Group_Area ga on ga.Area_guid = sikvd.areatype where CALLTYPE = 1 AND t1.rk = 1 AND t1.callstarttime > (convert(varchar(7),getdate(),120) + '-01') AND t1.callstarttime < convert(varchar(10),getdate(),120) AND sikvd.areatype IN('475AE8F5-8C60-0947-96D6-D4999CE07DF4','95ED5385-7379-1041-919A-BDF10A1CB5DC','74BCB8CD-D886-7B4D-BE0E-AAECE71FA6DE','ED48B3EA-347F-684F-99A3-9C0BA66B744F') Group By ga.area_desc order by charindex(ga.area_desc,'集团宝安龙岗光明')";
			eliteBean.update(todaysql_ins1, "dataSource");
			//写入数据
			def todaysql_ins2 = "UPDATE bigdatecall_month SET bigdatecall_month.[呼出电话数] = c.[呼出电话数],bigdatecall_month.[呼出成功率] = c.[呼出成功率] FROM (SELECT ga.Area_desc,COUNT(ga.Area_desc) '呼出电话数',LEFT(ROUND(SUM(CASE Callresult WHEN 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(ga.Area_desc),4)*100,CHARINDEX('.',ROUND(SUM(CASE Callresult WHEN 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(ga.Area_desc),4)*100) + 2) + '%' as 呼出成功率 FROM Call_Detail_View t1 LEFT JOIN staff s on s.agent_id = t1.Firstagent LEFT JOIN RolegroupArea rga on rga.Group_id = s.DEFAULTROLEGROUP LEFT JOIN Group_Area ga on ga.Area_guid = rga.Area_guid WHERE CALLTYPE = 4 AND t1.callstarttime > (convert(varchar(7),getdate(),120) + '-01') AND t1.callstarttime < convert(varchar(10),getdate(),120) AND ga.area_guid IN('475AE8F5-8C60-0947-96D6-D4999CE07DF4','95ED5385-7379-1041-919A-BDF10A1CB5DC','74BCB8CD-D886-7B4D-BE0E-AAECE71FA6DE','ED48B3EA-347F-684F-99A3-9C0BA66B744F') GROUP BY ga.Area_desc )c WHERE bigdatecall_month.区域 = c.Area_desc";
			eliteBean.update(todaysql_ins2, "dataSource");
			
			resulterror.put("code", "1");
			resulterror.put("msg", "每月话务数据写入完成");
			return  resulterror;
			
			}catch(Exception e){
				resulterror.put("code", "-1");
				resulterror.put("msg", "接口异常！"+e);
				return  resulterror;
			}
	}
}